package myJDBC.lowLevel;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import myJDBC.myConnection;
import oracle.jdbc.OracleTypes;

public class Objects {
	
	public static int createObject(String name, String descr, int objTypeId, int parId) throws SQLException{
		int i=-1;
		CallableStatement  cs= null;
		if(parId==-1){
			try {
				StringBuilder plsql = new StringBuilder();
						plsql.append("begin ")
				        .append("insert into \"objects\"(\"name\", \"description\", \"object_type_id\") values (?,?,?) ")
				        .append("RETURNING \"object_id\" ")
				        .append("INTO ?; ")
				        .append("END;");
				cs=myConnection.dbConnection.prepareCall(plsql.toString());
				cs.setString(1, name);
				cs.setString(2, descr);
				cs.setInt(3, objTypeId);
				cs.registerOutParameter(4, OracleTypes.NUMBER);
				cs.execute();
				i=cs.getInt(4);
			} finally {
				if (cs != null)
					cs.close();
			}	
		}else{
			try {
				StringBuilder plsql = new StringBuilder();
				   plsql.append("begin ")
				        .append("insert into \"objects\"(\"name\", \"description\", \"object_type_id\", \"parent_id\") values (?,?,?,?) ")
				        .append("RETURNING \"object_id\" ")
				        .append("INTO ?; ")
				        .append("END;");
				cs=myConnection.dbConnection.prepareCall(plsql.toString());
				cs.setString(1, name);
				cs.setString(2, descr);
				cs.setInt(3, objTypeId);
				cs.setInt(4, parId);
				cs.registerOutParameter(5, OracleTypes.NUMBER);
				cs.execute();
				i=cs.getInt(5);
			} finally {
				if (cs != null)
					cs.close();
			}	
		}
	return i;
	}

	public static ArrayList<String> returnAllObjInfo(){
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectObjects = new StringBuilder();
		  selectObjects.append("select t1.\"object_id\",t1.\"name\",t1.\"description\",t2.\"name\" as objtype ")
					   .append("from \"objects\" t1 ")
					   .append("left join \"object_type\" t2 ")
					   .append("on t1.\"object_type_id\" = t2.\"object_type_id\"");
		if (myConnection.dbConnection != null) {
			try {
				Statement statement = myConnection.dbConnection.createStatement();
				ResultSet rs = statement.executeQuery(selectObjects.toString());
				int i;
				while (rs.next()) {
					i = rs.getInt(1);
					al.add(Integer.toString(i));
					String tmp="";
					tmp = tmp+rs.getString(2)+"|| ";
					tmp = tmp+rs.getString(3)+"|| ";
					tmp = tmp+rs.getString(4);
					al.add(tmp);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;			
	}
	
	//id, name || desc
	public static ArrayList<String> returnObjInfoWithObjTypeID(int objTypeId){
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectObjects = new StringBuilder();
		  selectObjects.append("select t1.\"object_id\",t1.\"name\",t1.\"description\" ")
					   .append("from \"objects\" t1 ")
					   .append("where t1.\"object_type_id\"=? ");
		if (myConnection.dbConnection != null) {
			try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {
				statement.setInt(1, objTypeId);
				statement.executeUpdate();
				ResultSet rs = statement.executeQuery();
				int i;
				while (rs.next()) {
					i = rs.getInt(1);
					al.add(Integer.toString(i));
					String tmp="";
					tmp = tmp+rs.getString(2)+"|| ";
					tmp = tmp+rs.getString(3);
					al.add(tmp);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;			
	}

	public static String returnObjectTypeId(int objId) {
		String tmp="";
		StringBuilder selectAttributes = new StringBuilder();
		   selectAttributes.append("select \"object_type_id\" ")
				           .append("from \"objects\" ")
				           .append("where \"object_id\"=?");
		try (PreparedStatement pstmt = myConnection.dbConnection.prepareStatement(selectAttributes.toString())) {
			pstmt.setInt(1, objId);
			pstmt.executeUpdate();
			ResultSet rs = pstmt.executeQuery();			
			while (rs.next()) {
				tmp = rs.getString(1);			
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return tmp;
	}

	//name, descr, objtypename,  parentid, 
	public static ArrayList<String> returnObjInfo(int objId){
		ArrayList<String> al = new ArrayList<String>();
		StringBuilder selectObjects = new StringBuilder();
		   selectObjects.append("select t1.\"name\", t1.\"description\",t2.\"name\", t1.\"parent_id\" ")
						.append("from \"objects\" t1 ")
						.append("left join \"object_type\" t2 ")
						.append("on t1.\"object_type_id\" = t2.\"object_type_id\" ")
						.append("where \"object_id\"=? ");
		if (myConnection.dbConnection != null) {
			try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {
				statement.setInt(1, objId);
				statement.executeUpdate();
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					al.add(rs.getString(1));
					al.add(rs.getString(2));
					al.add(rs.getString(3));
					al.add(Integer.toString(rs.getInt(4)));
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;			
	}

	public static ArrayList<Integer> returnChildrens(int objId){
		ArrayList<Integer> al = new ArrayList<Integer>();
		StringBuilder selectObjects = new StringBuilder();
		   selectObjects.append("select \"object_id\" ")
						.append("from \"objects\" ")
						.append("where \"parent_id\"=? ");
		if (myConnection.dbConnection != null) {
			try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {
				statement.setInt(1, objId);
				statement.executeUpdate();
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					al.add(rs.getInt(1));
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return al;
		} else
			return null;		
	}
						  
	//[[attrid, attrname, attrvalue],[]] 
	public static ArrayList<ArrayList<String>> returnObjectAttributes(int objId, int objTypeId) throws NumberFormatException, SQLException{
		ArrayList<ArrayList<String>> ret = new ArrayList<ArrayList<String>>();
		ArrayList<String> attrIds= new ArrayList<String>();
		attrIds=ObjectTypes.returnObjectType(objTypeId);
		int barier = attrIds.indexOf(null);
		for (int i = barier + 1; i < attrIds.size(); i = i + 3) {			
			ArrayList<String> tmp = new ArrayList<String>();			
			if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(2)) || attrIds.get(i+2).equals(AttributeTypes.returnAttrType(1))){
				tmp.add(attrIds.get(i));
				tmp.add(attrIds.get(i+1));
				tmp.add(Params.returnVarchNumb(Integer.parseInt(attrIds.get(i)), objId));
				ret.add(tmp);
			}
			if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(3))){
				tmp.add(attrIds.get(i));
				tmp.add(attrIds.get(i+1));
				tmp.add(Params.returnDate(Integer.parseInt(attrIds.get(i)), objId));
				ret.add(tmp);
			}
			if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(4))){
				tmp.add(attrIds.get(i));
				tmp.add(attrIds.get(i+1));
				tmp.add(Params.returnArrValue(Integer.parseInt(attrIds.get(i)), objId));
				ret.add(tmp);
			}
			if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(5))){
				ArrayList<String> tmpal=References.returnNameObj(Integer.parseInt(attrIds.get(i)), objId);
				if(tmpal.size()!=0){
					tmp.add(attrIds.get(i));
					tmp.add(attrIds.get(i+1));
					tmp.add(tmpal.get(1));
					ret.add(tmp);
				}
			}
		}
		return ret;
	}
	
	//[[attrid, attrname, attrvalueID],[]]
		public static ArrayList<ArrayList<String>> returnObjectAttributesWithValId(int objId, int objTypeId) throws NumberFormatException, SQLException{
			ArrayList<ArrayList<String>> ret = new ArrayList<ArrayList<String>>();
			ArrayList<String> attrIds= new ArrayList<String>();
			attrIds=ObjectTypes.returnObjectType(objTypeId);
			int barier = attrIds.indexOf(null);
			for (int i = barier + 1; i < attrIds.size(); i = i + 3) {			
				ArrayList<String> tmp = new ArrayList<String>();			
				if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(2)) || attrIds.get(i+2).equals(AttributeTypes.returnAttrType(1))){
					tmp.add(attrIds.get(i));
					tmp.add(attrIds.get(i+1));
					tmp.add(Params.returnVarchNumb(Integer.parseInt(attrIds.get(i)), objId));
					ret.add(tmp);
				}
				if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(3))){
					tmp.add(attrIds.get(i));
					tmp.add(attrIds.get(i+1));
					tmp.add(Params.returnDate(Integer.parseInt(attrIds.get(i)), objId));
					ret.add(tmp);
				}
				if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(4))){
					tmp.add(attrIds.get(i));
					tmp.add(attrIds.get(i+1));
					tmp.add(Params.returnArrValueId(Integer.parseInt(attrIds.get(i)), objId));
					ret.add(tmp);
				}
				if(attrIds.get(i+2).equals(AttributeTypes.returnAttrType(5))){
						tmp.add(attrIds.get(i));
						tmp.add(attrIds.get(i+1));
						tmp.add((References.returnIdObj(Integer.parseInt(attrIds.get(i)),objId)).get(0).toString());
						ret.add(tmp);
					
				}
			}
			return ret;
		}

		//[[attrid, attrvalueID or attrvalue],[]]
		public static ArrayList<ArrayList<String>> returnObjectAttributesWithValIdNew(int objId, int objTypeId) throws SQLException{
			ArrayList<ArrayList<String>> ret = new ArrayList<ArrayList<String>>();
			ArrayList<ArrayList<String>> params = new ArrayList<ArrayList<String>>();
			ArrayList<String> tmp;
			params= Params.retParams(objId);//varch list date val
			for(int i=0;i<params.get(0).size(); i+=2){//id value  
				tmp  = new ArrayList<String>();
				String attrId=(params.get(0).get(i));
				tmp.add(attrId);
				tmp.add((Params.returnVarchNumb(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);
			}
			for(int i=0;i<params.get(1).size(); i+=2){//id value   
				String attrId=(params.get(1).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnArrValue(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);	
			}
			for(int i=0;i<params.get(2).size(); i+=2){//id value   
				String attrId=(params.get(2).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnDate(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);	
			}
			for(int i=0;i<params.get(3).size(); i+=2){//id value   
				String attrId=(params.get(3).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnDate(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);
			}
			//refs
			ArrayList<Integer> refs= new ArrayList<Integer>();
			refs= References.retRefereces(objId);
			for(int i=0;i<refs.size(); i+=2){//id value   
				tmp  = new ArrayList<String>();
				tmp.add(String.valueOf(refs.get(i)));
				tmp.add(String.valueOf(refs.get(i+1)));
				ret.add(tmp);
			}
			return ret;
		}
		
		public static ArrayList<ArrayList<String>> returnObjectAttributesWithValIdNewIdonly(int objId, int objTypeId) throws SQLException{
			ArrayList<ArrayList<String>> ret = new ArrayList<ArrayList<String>>();
			ArrayList<ArrayList<String>> params = new ArrayList<ArrayList<String>>();
			ArrayList<String> tmp;
			params= Params.retParams(objId);//varch list date val
			for(int i=0;i<params.get(0).size(); i+=2){//id value  
				tmp  = new ArrayList<String>();
				String attrId=(params.get(0).get(i));
				tmp.add(attrId);
				tmp.add((Params.returnVarchNumb(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);
			}
			for(int i=0;i<params.get(1).size(); i+=2){//id value   
				String attrId=(params.get(1).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnArrValueId(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);	
			}
			for(int i=0;i<params.get(2).size(); i+=2){//id value   
				String attrId=(params.get(2).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnDate(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);	
			}
			for(int i=0;i<params.get(3).size(); i+=2){//id value   
				String attrId=(params.get(3).get(i));
				tmp  = new ArrayList<String>();
				tmp.add(attrId);
				tmp.add((Params.returnDate(Integer.parseInt(attrId), objId)));	
				ret.add(tmp);
			}
			//refs
			ArrayList<Integer> refs= new ArrayList<Integer>();
			refs= References.retRefereces(objId);
			for(int i=0;i<refs.size(); i+=2){//id value   
				tmp  = new ArrayList<String>();
				tmp.add(String.valueOf(refs.get(i)));
				tmp.add(String.valueOf(refs.get(i+1)));
				ret.add(tmp);
			}
			return ret;
		}

		public static Map<String, Integer> returnChildrensName(int objId){
			Map<String, Integer> al = new HashMap<String, Integer>();
			StringBuilder selectObjects = new StringBuilder();
			   selectObjects.append("select \"name\", \"object_id\" ")
							.append("from \"objects\" ")
							.append("where \"parent_id\"=?");
			if (myConnection.dbConnection != null) {
				try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {				
					statement.setInt(1, objId);
					statement.executeUpdate();
					ResultSet rs = statement.executeQuery();
					while (rs.next()) {
						al.put(rs.getString(1), rs.getInt(2));
					}
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
				return al;
			} else
				return null;		
		}
		
		
		
		public static Map<Integer, Integer> returnMapObjInfo(String objName){
			Map<Integer, Integer> al = new HashMap<Integer, Integer>();
			StringBuilder selectObjects = new StringBuilder();
			   selectObjects.append("select \"object_id\", \"parent_id\" ")
							.append("from \"objects\" ")
							.append("where \"name\"=?");
			if (myConnection.dbConnection != null) {
				try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {				
					statement.setString(1, objName);
					statement.executeUpdate();
					ResultSet rs = statement.executeQuery();
					while (rs.next()) {
						al.put(rs.getInt(1), rs.getInt(2));
					}
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
				return al;
			} else
				return null;			
		}

		public static ArrayList<String> returnObjWithObjTypeName(String objTypeName){
			
			ArrayList<String> al = new ArrayList<String>();
			StringBuilder selectObjects = new StringBuilder();
			  selectObjects.append("select t1.\"object_id\",t1.\"name\" ")
						   .append("from \"objects\" t1 ")
						   .append("left join \"object_type\" t2 ")
						   .append("on t1.\"object_type_id\" = t2.\"object_type_id\" where t2.\"name\"=? ");
			if (myConnection.dbConnection != null) {
				try (PreparedStatement statement = myConnection.dbConnection.prepareStatement(selectObjects.toString())) {				
					statement.setString(1, objTypeName);
					statement.executeUpdate();
					ResultSet rs = statement.executeQuery();
					int i;
					while (rs.next()) {
						i = rs.getInt(1);
						al.add(Integer.toString(i));
						al.add(rs.getString(2));
					}
				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
				return al;
			} else
				return null;			
		}


}
